# -*- coding: utf8 -*-
'''
Created on 2013-5-26

@author: zengyixun
'''
import os,sys
isMySQLdb = True
Refernece = object
try:
    import MySQLdb
except:
    print "Warning:Import MySQLdb Error,try change to mysql.connector...."
    try:
        isMySQLdb = False
        import mysql
        import mysql.connector
        Refernece = mysql.connector.cursor.MySQLCursor
    except:
        print "Warning:Import MySQLdb Error and mysql.connector Error!"
try:
    import cx_Oracle as cx
    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
except:
    print "Warning:Import cx_Oracle Error"
import pdb


class MySQLCursorDict(Refernece):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

class DBOperation(object):
    
    def __init__(self,ip=None,port=3306,dbservice=None,charset='utf8',user=None,passwd=None):
        '''
        @param db:mysql is dbname,oracle is servicename 
        '''
        self.DBInfo = {}
        self.DBInfo["ip"] = ip
        self.DBInfo["port"] =port
        self.DBInfo["user"] = user
        self.DBInfo["passwd"] = passwd
        self.DBInfo["db"] = dbservice
        self.DBInfo["charset"]=charset
        
    def OracleDataSet(self,Datas=[]):
        return  self.DataSet(Datas, Type="oracle")
    
    def MysqlDataSet(self,Datas=[]):
        return self.DataSet(Datas)
        
    def DataSet(self,Datas=[],Type="mysql"):
        '''
        @param Datas:[{"sql":"sqlStr","param":Tuple},...] 
                    mysql sql like this:'insert into fio_reportdata(name,blocksize,testcaseid,iops) values(%s,%s,%s,%s)' , param Tuple like this (name,blocksize,testcaseid,iops,)
                    oralce sql like this:'SELECT * FROM locations WHERE country_id=:1 AND city=:2',param Tuple like this:('US', 'Seattle',)
        '''
        return DataSet(Datas,Type,self.DBInfo)
    
class DataSet:
    '''
    @note: 非线程安全
    '''
    def __init__(self,Datas,Type,DBInfo):
        self.Datas = Datas
        self.Type = Type.lower()
        self.DBInfo = DBInfo
        self.connect = None
        self.cur = None
        self.tnsname = None
        
    def Init(self):
        self.__connect__()

        
    def __connect__(self):
        if self.Type=="mysql":
            if isMySQLdb:
                self.connect = MySQLdb.connect(host=self.DBInfo["ip"],user=self.DBInfo["user"],db=self.DBInfo["db"],
                                      passwd=self.DBInfo["passwd"],port=self.DBInfo["port"],charset=self.DBInfo["charset"])
                self.cur=self.connect.cursor(cursorclass = MySQLdb.cursors.DictCursor)
            else:
                self.connect = mysql.connector.connect(host=self.DBInfo["ip"],user=self.DBInfo["user"],database=self.DBInfo["db"],
                                      password=self.DBInfo["passwd"],port=self.DBInfo["port"],charset=self.DBInfo["charset"])
                self.cur = self.connect.cursor(cursor_class=MySQLCursorDict)
        elif self.Type=="oracle":
            self.tnsname = cx.makedsn(self.DBInfo["ip"],self.DBInfo["port"],self.DBInfo["db"])
            self.connect = cx.connect(self.DBInfo["user"],self.DBInfo["passwd"],self.tnsname)
            self.cur = self.connect.cursor()
    
    def __close__(self):
        if self.Type=="mysql":
            self.cur.close()
            self.connect.close()
        elif self.Type=="oracle":
            self.cur.close()
            self.connect.close()
            self.connect =None
            self.cur = None
            
            
    def getclobdata(self,filename):
        if self.Type=="oracle":
            clob_data = self.cur.var(cx.CLOB)
            f= open(filename,'r').read()
            clob_data.setvalue(0,f) 
            return clob_data
        else:
            return "<CLOB>"
        
    def do(self,sql=None,param=None):
        ret = 0
        if self.connect==None:
            self.__connect__()
        try:
            if sql<>None:
                if param==None:
                    param = ()
            if sql<>None and param<>None:
                if self.Type=="mysql":
                    ret = self.cur.execute(sql,param)
                elif self.Type=="oracle":
                    self.connect.begin()
                    self.cur.execute(sql,param)
                    ret += self.cur.rowcount
            else:
                if self.Type=="oracle":
                    self.connect.begin()
                try:
                    for idata in self.Datas:
                        sql = idata["sql"]
                        param = idata["param"]
                        if self.Type=="mysql":
                            ret += self.cur.execute(sql,param)
                        elif self.Type=="oracle":
                            self.cur.execute(sql,param)
                            ret += self.cur.rowcount
                except:
                    self.connect.rollback()
                    print sys.exc_info()[0],sys.exc_info()[1]
                    return 0
            self.connect.commit()
#         except:
#             self.connect.rollback()
        finally:
            self.__close__()
        return ret
    
    def callproc(self,procname,param=()):
        self.__connect__()
        self.cur.callproc(procname,param)
        self.connect.commit()
#         pdb.set_trace()
        self.__close__()

        
    def select(self,sql,param,isClob=False):
        '''
        @param sql: oracle like this: SELECT * FROM locations WHERE country_id=:1 AND city=:2
        @param sql: mysql like this: SELECT * FROM locations WHERE country_id=%s AND city=%s
        @param param: like this: ('US', 'Seattle',)
        '''
        if sql==None or param==None:
            return
        rs = (0,None)
        self.__connect__()
        try:
            if self.Type=="mysql":
                if param==None:
                    param = ()
                if isMySQLdb:
                    rnum = self.cur.execute(sql,param)
                    if rnum>0:
                        recordall = self.cur.fetchall()
                        rs = (rnum,recordall)
                else:
                    self.cur.execute(sql,param)
                    rows = self.cur.fetchall()
                    if rows==None:
                        return (0,None)
                    else:
                        return (len(rows),rows)
            elif self.Type=="oracle":
                if len(param)==0:
                    r = self.cur.execute(sql)
                else:
                    r = self.cur.execute(sql,param)
                des=[]
                for row in r.description:
                    des.append(row[0])
                print des
                rows= []
                for row in r:
                    d = dict(zip(des,[x for x in row]))
                    rows.append(d)
                rnum = len(rows)
                if rnum<=0:
                    rows = None
                rs = (rnum,rows)
        finally:
            self.__close__()
        return rs
    
    
if __name__=='__main__':
    print dir(cx)
        
            
        
        
        
        
        
        
        
        
    